@Q=("select * from bench1 where !id!=3 or !id!=2 or !id!=1 or !id!=4 or !id!=16 or !id!=10",
6,
"select * from bench1 where !id!>=" . ($total_rows-1) ." or !id!<1",
2,
"select * from bench1 where !id!>=1 and !id!<=2",
2,
"select * from bench1 where (!id!>=1 and !id!<=2) or (!id!>=1 and !id!<=2)",
2,
"select * from bench1 where !id!>=1 and !id!<=10 and !id!<=5",
5,
"select * from bench1 where (!id!>0 and !id!<2) or !id!>=" . ($total_rows-1),
2,
"select * from bench1 where (!id!>0 and !id!<2) or (!id!>= " . ($opt_loop_count/2) . " and !id! <= " . ($opt_loop_count/2+2) . ") or !id! = " . ($opt_loop_count/2-1),
5,
"select * from bench1 where (!id!>=5 and !id!<=10) or (!id!>=1 and !id!<=4)",
10,
"select * from bench1 where (!id!=1 or !id!=2) and (!id!=3 or !id!=4)",
0,
"select * from bench1 where (!id!=1 or !id!=2) and (!id!=2 or !id!=3)",
1,
"select * from bench1 where (!id!=1 or !id!=5 or !id!=20 or !id!=40) and (!id!=1 or !id!>=20 or !id!=4)",
3,
"select * from bench1 where ((!id!=1 or !id!=3) or (!id!>1 and !id!<3)) and !id!<=2",
2,
"select * from bench1 where (!id! >= 0 and !id! < 4) or (!id! >=4 and !id! < 6)",
6,
"select * from bench1 where !id! <= -1 or (!id! >= 0 and !id! <= 5) or (!id! >=4 and !id! < 6) or (!id! >=6 and !id! <=7) or (!id!>7 and !id! <= 8)",
9,
"select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>=0 and !id! <=10)",
11,
"select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>2 and !id! <=10)",
10,
"select * from bench1 where (!id!>1 or !id! <1) and !id!<=2",
2,
"select * from bench1 where !id! <= 2 and (!id!>1 or !id! <=1)",
3,
"select * from bench1 where (!id!>=1 or !id! <1) and !id!<=2",
3,
"select * from bench1 where (!id!>=1 or !id! <=2) and !id!<=2",
3
);
print "\nTest of compares with simple ranges\n";
check_select_range("id","select_range_prefix");
check_select_range("id3","select_range_key2");
####
#### Some group queries
####
if ($limits->{'group_functions'})
{
$loop_time=new Benchmark;
$count=1;
$estimated=0;
for ($tests=0 ; $tests < $small_loop_count ; $tests++)
{
$sth=$dbh->prepare($query="select count(*) from bench1") or die $DBI::errstr;
$sth->execute or die $sth->errstr;
if (($sth->fetchrow_array)[0] != $total_rows)
{
print "Warning: '$query' returned wrong result\n";
}
$sth->finish;
# min, max in keys are very normal
$count+=7;
fetch_all_rows($dbh,"select min(id) from bench1");
fetch_all_rows($dbh,"select max(id) from bench1");
fetch_all_rows($dbh,"select sum(id+0.0) from bench1");
fetch_all_rows($dbh,"select min(id3),max(id3),sum(id3-0.0) from bench1");
if ($limits->{'group_func_sql_min_str'})
{
fetch_all_rows($dbh,"select min(dummy1),max(dummy1) from bench1");
}
$count++;
$sth=$dbh->prepare($query="select count(*) from bench1 where id >= " .
($opt_loop_count*2)) or die $DBI::errstr;
$sth->execute or die $DBI::errstr;
if (($sth->fetchrow_array)[0] != $opt_loop_count)
{
print "Warning: '$query' returned wrong result\n";
}
$sth->finish;
$count++;
$sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr;
print "Warning: '$query' returned wrong result: @row\n";
}
$sth->finish;
if ($limits->{'func_odbc_mod'})
{
$tmp="mod(id,10)";
if ($limits->{'func_extra_%'})
{
$tmp="id % 10"; # For postgreSQL
}
$count++;
if ($limits->{'group_by_alias'}) {
if (fetch_all_rows($dbh,$query=$server->query("select $tmp as last_digit,count(*) from bench1 group by last_digit")) != 10)
{
print "Warning: '$query' returned wrong number of rows\n";
}
} elsif ($limits->{'group_by_position'}) {
if (fetch_all_rows($dbh,$query=$server->query("select $tmp,count(*) from bench1 group by 1")) != 10)
{
print "Warning: '$query' returned wrong number of rows\n";
}
}
}
if ($limits->{'order_by_position'} && $limits->{'group_by_position'})
{
$count++;
if (fetch_all_rows($dbh, $query="select id,id3,dummy1 from bench1 where id < 100+$count-$count group by id,id3,dummy1 order by id desc,id3,dummy1") != 100)
{
print "Warning: '$query' returned wrong number of rows\n";
}
}
$end_time=new Benchmark;
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is not null");
}
$end_time=new Benchmark;
print "Time for outer_join_found ($small_loop_count:$count): " .
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is null");
$end_time=new Benchmark;
last if ($estimated=predict_query_time($loop_time,$end_time,
\$count,$i,
$range_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for outer_join_not_found ($range_loop_count:$count): " .